package com.jlastu.androiduidemo.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.jlastu.androiduidemo.bean.Course;
import com.jlastu.androiduidemo.bean.Student;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {

   private static final String TAG = "DBHelper";
   private static final String DATABASE_NAME = "db_student_manager";// 数据库名
   private static final int DATABASE_VERSION = 1;// 数据库版本号
   private static final String TABLE_NAME_STUDENT = "student"; //学生表
   private static final String TABLE_NAME_COURSE = "course"; //课程表
   private static final String TABLE_NAME_STUDENT_COURSE = "student_course"; // 关联表
   private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表

   private static DBHelper mHelper = null;
   private DBHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   //单例模式
   public static DBHelper getInstance(Context context){
      if(mHelper == null){
         mHelper = new DBHelper(context);
      }
      return mHelper;
   }

   //数据库创建时被调用
   @Override
   public void onCreate(SQLiteDatabase db) {
      initTable(db);
      initData(db);
   }

   //数据库升级
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      if (oldVersion < 2) { // 数据库 1 -> 2 升级
         // 创建设置表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (keycolumn text primary key,valuecolumn text)");
      }
   }

   /**
    * 初始化数据表
    *
    * @param db 数据库
    */
   private void initTable(SQLiteDatabase db) {
      // 创建学生表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT + " (" +
              "_id integer primary key autoincrement," +
              "student_name text," +
              "student_code text," +
              "username text," +
              "password text," +
              "class_name text)");
      // 创建课程表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_COURSE + " (" +
              "_id integer primary key autoincrement," +
              "course_name text," +
              "course_code text," +
              "course_teacher text," +
              "learning_hours text)");
      // 创建选课表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT_COURSE + " (" +
              "student_id integer," +
              "course_id  integer," +
              "FOREIGN KEY (student_id) REFERENCES student(_id)," +
              "FOREIGN KEY (course_id) REFERENCES course(_id))");
   }

   /**
    * 初始化数据库内部模拟数据(仅在第一次安装时执行)
    *
    * @param db 数据库
    */
   private void initData(SQLiteDatabase db) {
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('奥利奥', '202100000001', '奥兹', '222', '计算机1班')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('马加七', '202100000002', '加七', '222', '计算机1班')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('迪迦', '202100000003', '加纳', '111', '计算机3班')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('白小飞', '202100000004', '飞鸽', '333', '计算机4班')");

      db.execSQL("insert into " + TABLE_NAME_COURSE +
              " (course_name , course_code, course_teacher, learning_hours) " +
              "values" +
              "('Android', '99996666', '张老师', '80')");
      db.execSQL("insert into " + TABLE_NAME_COURSE +
              " (course_name , course_code, course_teacher, learning_hours) " +
              "values" +
              "('Java', '8888aaaa', '黄老师', '48')");
      db.execSQL("insert into " + TABLE_NAME_COURSE +
              " (course_name , course_code, course_teacher, learning_hours) " +
              "values" +
              "('Web', '6666cccc', '周老师', '64')");
   }

   public void insertStudent(Student student) {
      SQLiteDatabase db = getWritableDatabase();
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('" + student.getStudent_name() + "','" + student.getStudent_code() + "','" + student.getUsername() + "','" + student.getPassword() + "','" + student.getClass_name() + "')");
   }
   public void insertCourse(Course course){
      SQLiteDatabase db = getWritableDatabase();
      db.execSQL("insert into" + TABLE_NAME_COURSE +
              "(course_name,course_code,course_teacher,learning_hours)" +
              "values" +
              "('" + course.getCourse_name() +"','"+course.getCourse_code()+"','"+course.getCourse_teacher() +"','" +course.getLearning_hours() +"')");
   }

   //添加学生
   public int insertStudent2(Student student) {
      //获取可写数据库
      SQLiteDatabase db = getWritableDatabase();

      //创建ContentValues以Key-Value的形式封装数据
      ContentValues values = new ContentValues();
      values.put("student_name", student.getStudent_name());
      values.put("student_code", student.getStudent_code());
      values.put("username", student.getUsername());
      values.put("password", student.getPassword());
      values.put("class_name", student.getClass_name());

      //执行insert方法，插入数据
      long newRowId = db.insert(TABLE_NAME_STUDENT, null, values);
      Log.d(TAG, "insertStudent2 newRowId = " + newRowId);
      return (int) newRowId;
   }
   public int insertCourse2(Course course){
      SQLiteDatabase db =getWritableDatabase();
      ContentValues values =new ContentValues();
      values.put("course_name",course.getCourse_name());
      values.put("course_code",course.getCourse_code());
      values.put("course_teacher",course.getCourse_teacher());
      values.put("learning_hours",course.getLearning_hours());

      long newRowId = db.insert(TABLE_NAME_COURSE,null,values);
      Log.d(TAG,"insertCourse newRowId ="+ newRowId);
      return (int) newRowId;
   }

   //根据名字删除学生
   public void deleteStudentByName(String name) {
      if (name != null) {
         SQLiteDatabase db = getReadableDatabase();
         int changedRowCount = db.delete(TABLE_NAME_STUDENT, "student_name = ?", new String[]{name});
         Log.d(TAG, "deleteStudentByName changedRowCount = " + changedRowCount);
      }
   }
   public void deleteCourseByName(String name) {
      if (name != null) {
         SQLiteDatabase db = getReadableDatabase();
         int changedRowCount = db.delete(TABLE_NAME_COURSE, "course_name = ?", new String[]{name});
         Log.d(TAG, "deleteCoursetByName changedRowCount = " + changedRowCount);
      }
   }

   //根据id删除学生
   public void deleteStudentById(int id) {
      if (id >= 0) {
         SQLiteDatabase db = getReadableDatabase();
         int changedRowCount = db.delete(TABLE_NAME_STUDENT, "_id = ?", new String[]{String.valueOf(id)});
         Log.d(TAG, "deleteStudentById changedRowCount = " + changedRowCount);
      }
   }

   public void deleteCourseById(int id) {
      if (id >= 0) {
         SQLiteDatabase db = getReadableDatabase();
         int changedRowCount = db.delete(TABLE_NAME_COURSE, "_id = ?", new String[]{String.valueOf(id)});
         Log.d(TAG, "deleteCourseById changedRowCount = " + changedRowCount);
      }
   }

   //根据id修改学生名字
   public void updateStudentNameById(String name, int id) {
      if (name != null && id >= 0) {
         SQLiteDatabase db = getReadableDatabase();
         ContentValues values = new ContentValues();
         values.put("student_name", name);
         int changedRowCount = db.update(TABLE_NAME_STUDENT, values, "_id = ?", new String[] {String.valueOf(id)});
         Log.d(TAG, "updateStudentNameById changedRowCount = " + changedRowCount);
      }
   }
   public void updateCourseNameById(String name, int id) {
      if (name != null && id >= 0) {
         SQLiteDatabase db = getReadableDatabase();
         ContentValues values = new ContentValues();
         values.put("Course_name", name);
         int changedRowCount = db.update(TABLE_NAME_COURSE, values, "_id = ?", new String[] {String.valueOf(id)});
         Log.d(TAG, "updateCourseNameById changedRowCount = " + changedRowCount);
      }
   }

   //查询所有学生
   @SuppressLint("Range")
   public List<Student> selectAllStudent() {
      List<Student> list = new ArrayList<>();
      SQLiteDatabase db = getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_STUDENT,     // 表名
              null,                   // 要查询的列数据
              null,                   // 条件语句
              null,                   // 条件语句的值
              null,                   // group by
              null,                   // having
              null                    // 排序语句
      );
      if (cursor != null && cursor.getCount() > 0) {
         while (cursor.moveToNext()) {
            Student student = new Student();
            student.setStudent_id(cursor.getInt(cursor.getColumnIndex("_id")));
            student.setStudent_name(cursor.getString(cursor.getColumnIndex("student_name")));
            student.setStudent_code(cursor.getString(cursor.getColumnIndex("student_code")));
            student.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            student.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            student.setClass_name(cursor.getString(cursor.getColumnIndex("class_name")));
            list.add(student);
         }
      }
      Log.d(TAG, "selectAllStudent = " + list.size());
      return list;
   }
   @SuppressLint("Range")
   public List<Course> selectAllCourse(){
      List<Course> list = new ArrayList<>();
      SQLiteDatabase db =getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_COURSE,//
              null,//
              null,//
              null,//
              null,//
              null,//
              null//
      );

      if (cursor !=null && cursor.getCount()>0){
         while (cursor.moveToNext()){
            Course course = new Course();
            course.setCourse_name(cursor.getString(cursor.getColumnIndex("course_name")));
            course.setCourse_code(cursor.getString(cursor.getColumnIndex("course_code")));
            course.setCourse_teacher(cursor.getString(cursor.getColumnIndex("course_teacher")));
            course.setLearning_hours(cursor.getString(cursor.getColumnIndex("learning_hours")));
            list.add(course);
         }
      }
      Log.d(TAG,"selectAllCourse ="+ list.size());
      return list;
   }

//   @SuppressLint("Range")
//   public List<Course> selectAllcourse(){
//      List<Course> list = new ArrayList<>();
//      SQLiteDatabase db =getReadableDatabase();
//
//      Cursor cursor = db.query(
//              TABLE_NAME_COURSE,
//              null,
//              "(course_name = ? or course_code = ? or course_teacher = ?) and learning_hours = ?", // 条件语句
//              new String[] {},
//              null,
//              null
//      );
//      if (cursor !=null && cursor.getCount()>0){
//         while (cursor.moveToNext()){
//            Course course = new Course();
//            course.setCourse_name(cursor.getColumnIndex("course_name"));
//            course.setCourse_code(cursor.getColumnIndex("course_code"));
//            course.setCourse_teacher(cursor.getColumnIndex("course_teacher");
//            course.setLearning_hours(cursor.getColumnIndex("learning_hours"));
//            list.add(course);
//         }
//      }
//      Log.d(TAG,"selectAllCourse ="+ list.size());
//      return list;
//   }

   //登录
   @SuppressLint("Range")
   public Student login(String username, String password) {
      SQLiteDatabase db = getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_STUDENT,                                                        // 表名
              null,                                                                      // 要查询的列数据
              "(username = ? or student_name = ? or student_code = ?) and password = ?", // 条件语句
              new String[] {username, username, username, password},                     // 条件语句的值
              null,                                                                      // group by
              null,                                                                      // having
              null                                                                       // 排序语句
      );
      Student student = null;
      if (cursor != null && cursor.getCount() > 0) {
         if (cursor.moveToFirst()) {
            student = new Student();
            student.setStudent_id(cursor.getInt(cursor.getColumnIndex("_id")));
            student.setStudent_name(cursor.getString(cursor.getColumnIndex("student_name")));
            student.setStudent_code(cursor.getString(cursor.getColumnIndex("student_code")));
            student.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            student.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            student.setClass_name(cursor.getString(cursor.getColumnIndex("class_name")));
            Log.d(TAG, "login = " + student.toString());
         }
      }

      return student;
   }

   //根据id查询学生信息
   @SuppressLint("Range")
   public Student getStudentById(int id) {
      SQLiteDatabase db = getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_STUDENT,                                                        // 表名
              null,                                                                      // 要查询的列数据
              "_id = ?",                                                                 // 条件语句
              new String[] {String.valueOf(id)},                                         // 条件语句的值
              null,                                                                      // group by
              null,                                                                      // having
              null                                                                       // 排序语句
      );
      Student student = null;
      if (cursor != null && cursor.getCount() > 0) {
         if (cursor.moveToFirst()) {
            student = new Student();
            student.setStudent_id(cursor.getInt(cursor.getColumnIndex("_id")));
            student.setStudent_name(cursor.getString(cursor.getColumnIndex("student_name")));
            student.setStudent_code(cursor.getString(cursor.getColumnIndex("student_code")));
            student.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            student.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            student.setClass_name(cursor.getString(cursor.getColumnIndex("class_name")));
            Log.d(TAG, "current student = " + student.toString());
         }
      }

      return student;
   }
}
